//
//  DataBaseHandle.m
//  TrekkingTravel
//
//  Created by dlios on 15/8/21.
//  Copyright (c) 2015年 TrekkingTravel. All rights reserved.
//

#import "DataBaseHandle.h"

@implementation DataBaseHandle

+ (instancetype)shareDataBase
{
    static DataBaseHandle *dataBase = nil;
    if (dataBase == nil) {
        dataBase = [[DataBaseHandle alloc] init];
    }
    return dataBase;
}
// 1.导入数据库类库 libsqlite3.0
// 2.创建单例
// 3.引入头文件,创建数据库对象 db;

static sqlite3 *db = nil;

// 4.打开数据库
- (void)OpenDB
{
    if (db != nil) {
        return;//如果数据库对象存在则不往下执行
    }
    
    //创建数据库文件路径
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *filePath = [array lastObject];
    NSString *sqlitePath = [filePath stringByAppendingPathComponent:@"dataBase.sqlite"];
    //    NSLog(@"sqlitePath ===== %@",sqlitePath);
    //打开数据库函数 将数据库对象与数据库关联,并打开数据库
    int result = sqlite3_open(sqlitePath.UTF8String, &db);
    if (result == SQLITE_OK) {
        //        NSLog(@"数据库打开成功");
    }else {
        //        NSLog(@"数据库打开失败");
    }
    
}
- (void)createTable
{
    //写创建表的SQL语句
    NSString *sql = @"CREATE TABLE IF NOT EXISTS collections (number INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, url TEXT)";
    //执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //        NSLog(@"表创建成功");
    }else {
        //        NSLog(@"表创建失败");
    }
}
- (void)insertCollections:(MineModel *)collectionM
{
    //number 是主键,自从递增
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO collections (title,url) VALUES('%@','%@')",collectionM.title,collectionM.url];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //        NSLog(@"数据插入成功");
    }else {
        //        NSLog(@"数据插入失败");
    }
}
- (NSMutableArray *)selectAll
{
    NSString *sql = @"SELECT * FROM collections";
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt = nil;
    NSMutableArray *array = [NSMutableArray array];
    //查询准备操作
    //参数3 -1代表不限制sel语句的长度
    //主要作用是将数据库对象db,sql语句,数据库跟指针关联到一起,以便数据库进行查询操作;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        //        NSLog(@"准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            //取出表里每行中的每个字段数据
            int number = sqlite3_column_int(stmt, 0);
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *url = sqlite3_column_text(stmt, 2);
            
            MineModel *collectionM = [[MineModel alloc] init];
            collectionM.title = [NSString stringWithUTF8String:(const char *)title];
            collectionM.url = [NSString stringWithUTF8String:(const char *)url];
            collectionM.number = number;
            [array addObject:collectionM];
            
        }
        return array;
    }else {
        //        NSLog(@"准备失败");
        return nil;//或者return array;
    }
}
- (void)deleteCollection:(NSInteger)number
{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM collections WHERE number = %ld",number];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //        NSLog(@"删除成功");
    }else {
        //        NSLog(@"删除失败");
    }
}



@end
